<?php
error_reporting(0);
date_default_timezone_set('Europe/London');

require_once 'Classes/PHPExcel/IOFactory.php';
require_once 'conn.ini';

$objReader = PHPExcel_IOFactory::createReader('Excel5');

$template = count(explode('-', $_GET['ids']));
$template_file = 'PaymentTrackingReport.xls';
$objPHPExcel = $objReader->load("templates/".$template_file);

$data = null;
//echo $_GET['ids'];
$ids = str_replace("-", "," , $_GET['ids']);
//echo $ids;
$sql = "SELECT * FROM cc_paymentschedules WHERE id IN (" . $ids . ")";

$rs = mysql_query($sql);
if(mysql_num_rows($rs)) {
	$i=0;
	while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
        $markets = json_decode($row['markets']);
        $placement_paid = json_decode($row['placement_paid'], true);
        // repair data
        $temps = array();
        foreach ($markets as $key => $arr['Paymentschedule']) {
            $arr['Paymentschedule']['contact_name'] = $arr['Paymentschedule'][0];
            $arr['Paymentschedule']['paymentid'] = $row['id'];
            $arr['Paymentschedule']['paymentrefid'] = $row['refid'];
            $arr['Paymentschedule']['currency'] = $row['currency'];
            
            $sql = "SELECT * FROM cc_accountresumes WHERE refid = '" . $row['accountresume_id'] . "'";
            $sub_rs = mysql_query($sql);
            $accid = mysql_fetch_array($sub_rs, MYSQL_ASSOC);
            
            $arr['Paymentschedule']['accid'] = $accid['id'];
            $arr['Paymentschedule']['refid_edit'] =  $row['refid_edit'];
            $arr['Paymentschedule']['paid_amount'] =  $row['paid_amount'];
            if($row['paid_amount'] == $row['broker_amount']){
                $arr['Paymentschedule']['alltoaih'] = 1;
            }else{
                $arr['Paymentschedule']['alltoaih'] = 0;
            }
            $arr['Paymentschedule']['paid_to_aih'] = $row['paid_to_aih'];
            $arr['Paymentschedule']['refid'] = $key;
            $arr['Paymentschedule']['broker_amount'] = $row['broker_amount'];;
            $arr['Paymentschedule']['contact_amount'] = number_format($arr['Paymentschedule'][3],2).' '.$row['currency'];

            if(empty($placement_paid)){
                $arr['Paymentschedule']['paid'] = false;
                if($arr['Paymentschedule'][2] == '0000-00-00') {
                    $arr['Paymentschedule']['paid_date'] = '';
                } else {
                    $arr['Paymentschedule']['paid_date'] = date('m/d/Y', strtotime($arr['Paymentschedule'][2]));
                }
            }else{
                $arr['Paymentschedule']['paid'] = $placement_paid[$key]['paid'];
                if($arr['Paymentschedule']['paid'] == 'on'){
                    $arr['Paymentschedule']['paid'] = 1;
                }else{
                    $arr['Paymentschedule']['paid'] = 0;
                }
                $arr['Paymentschedule']['paid_date'] = date('m/d/Y', strtotime($placement_paid[$key]['paid_date']));
            }
            $temps[] = $arr;
        }
        $arr['Paymentschedule']['contact_name'] = 'AIH Commission';
        $arr['Paymentschedule']['contact_amount'] = $row['aih_amount'];
        $temps[] = $arr;
        $a[] = $temps;
		$i++;
	}
}

// data
//$data = array(
//    array(
//        'to' => array(
//          'To Beema Pakistan',
//          'To AIH Commission'
//        ),
//        'to_checked' => array(
//          '√',
//          ''
//        ),
//        'payment_id' => 'AIH-PAYMENT-4F843380B7DCB',
//        'reference_id' => '142 / 1143-MAHM-122011',
//        'amount' => 'IDR 32,659,091.67',
//        'paid_amount' => 'IDR 0.00',
//        '100_to_aih' => ''
//    )
//);

$arr_col = array(0 => 'G', 1 => 'H', 2 => 'I', 3 => 'J', 4 => 'K', 5 => 'L', 6 => 'M', 7 => 'N', 8 => 'O', 9 => 'P');
$arr_col_copy = array(0 => 'B', 1 => 'C', 2 => 'D', 3 => 'E', 4 => 'F', 5 => 'G', 6 => 'H', 7 => 'I', 8 => 'J', 9 => 'K', 10 => 'L', 11 => 'M', 12 => 'N', 13 => 'O', 14 => 'P', 15 => 'Q', 16 => 'R');
$max = 2;
$arr_max = null;
$i = 0;
foreach ($a as $key => $value) {
    $max = (count($value) > $max) ? count($value) : $max;
    $arr_max[$i] = count($value);
    $totalamount = 0;
    foreach ($value as $v) {
        $totalamount = $totalamount + $v['Paymentschedule']['contact_amount'];
        $checked = ($v['Paymentschedule']['paid'] == '1') ? '√' : '';
        if($v['Paymentschedule']['contact_name'] == 'AIH Commission'){
            $checked = ($v['Paymentschedule']['paid_to_aih'] == 1) ? '√' : '';
        }
        $data[$i]['to'][] = 'To ' . $v['Paymentschedule']['contact_name'];
        $data[$i]['to_checked'][] = $checked;
    }
    $data[$i]['payment_id'] = $value[1]['Paymentschedule']['paymentrefid'];
    $data[$i]['reference_id'] = $value[1]['Paymentschedule']['refid_edit'];
    $data[$i]['amount'] = $value[1]['Paymentschedule']['currency'] . ' ' . number_format($value[1]['Paymentschedule']['broker_amount'], 2);
    $data[$i]['paid_amount'] = $value[1]['Paymentschedule']['currency'] . ' ' . number_format($value[1]['Paymentschedule']['paid_amount'], 2);
    $data[$i]['100_to_aih'] = $value[0]['Paymentschedule']['alltoaih'] == 1 ? '√' : '';
    
    $i++;
}

$baseRowCopy = 10;
$baseRowCopy2 = 11;
$baseRowRepeat = 13;
$baseRowRepeat2 = 14;

//echo '<pre>';
//print_r($data);die;

$baseRow = 11;
if($data != null) {
    // col process
    if($max > 2) {
        for($i_col = 0; $i_col < $max - 2; $i_col++) {
            $objPHPExcel->getActiveSheet()->insertNewColumnBefore('H',1);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        }
    }
    
    // repeat
    $max_col = 5 + $max;
    for($i = 1; $i < $template; $i++) {
        $objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRowRepeat,1);
        $objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRowRepeat,1);
        $objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRowRepeat,1);
        
        for($j = 0; $j < $max_col; $j++) {
            $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle($arr_col_copy[$j] . $baseRowCopy), $arr_col_copy[$j] . $baseRowRepeat );
            $objPHPExcel->getActiveSheet()->setCellValue($arr_col_copy[$j] . $baseRowRepeat, $objPHPExcel->getActiveSheet()->getCell($arr_col_copy[$j] . $baseRowCopy)->getValue());
            $objPHPExcel->getActiveSheet()->getRowDimension($baseRowRepeat)->setRowHeight(-1);
            
            $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle($arr_col_copy[$j] . $baseRowCopy2), $arr_col_copy[$j] . $baseRowRepeat2 );
        }
        
        $baseRowRepeat = $baseRowRepeat + 3;
        $baseRowRepeat2 = $baseRowRepeat2 + 3;
    }
    
	$index = 0;
    $step = 0;
    foreach($data as $r => $dataRow) {
        $row = $baseRow + $step;
        $row_title = $row - 1;
        
        // row title
        for($i_col = 0; $i_col < $arr_max[$r]; $i_col++) {
            $objPHPExcel->getActiveSheet()->setCellValue($arr_col[$i_col].$row_title, $dataRow['to'][$i_col]);
//            $objPHPExcel->getActiveSheet()->getColumnDimension($arr_col[$i_col])->setWidth(-1);
            $objPHPExcel->getActiveSheet()->getRowDimension($row_title)->setRowHeight(-1);
            
            $objPHPExcel->getActiveSheet()->setCellValue($arr_col[$i_col].$row, $dataRow['to_checked'][$i_col]);
        }
        
//        $objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);
		
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $dataRow['payment_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $dataRow['reference_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $dataRow['amount']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, $dataRow['paid_amount']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, $dataRow['100_to_aih']);
		$objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(-1);
        $index++;
        $step += 3;
    }
}

mysql_close();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="PaymentTrackingReport.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
